#coding=utf-8

if __name__ == '__main__':
    import os
    import MySQLdb
    
    sql_files = []
    for file in os.listdir('./update_sql'):
        sql_files.append(file[:10])
        
    sql_files = sorted(sql_files)
    # print sorted(sql_file)
    # print reversed(sql_file)
    # s = reversed(sql_file)
    # print s
    # for t in reversed(sql_file):
        # print t 
    
    conn = MySQLdb.connect(host='127.0.0.1', user='root',passwd='admin',
                        db='myshop', charset='utf8')
                
    #获取操作游标  
    cursor = conn.cursor()    
    
    cursor.execute('SET NAMES utf8;') 
    cursor.execute('SET CHARACTER SET utf8;')
    cursor.execute('SET character_set_connection=utf8;')                
        
    try:
        print '**********begin update************'
        conn.begin()
        cnt = cursor.execute("select version,post_date from version where id=1;")
        if cnt == 0:
            for file in sql_files:
                with open('./update_sql/'+file+'.sql') as f:
                    cursor.execute(f.read())

        
        version,post_date = cursor.fetchone()    
        for file in sql_files:
            if file > post_date.strftime('%Y-%m-%d'):
                with open('./update_sql/'+file+'.sql') as f:
                    cursor.execute(f.read())                    
        conn.commit()        
    except:
        conn.rollback()
        print '**********rollback************'
        print u'发生异常，进行回滚操作'
    finally:
        print '**********finally************'    
        # _mysql_exceptions.OperationalError: (1060, "Duplicate column name 'audit'")
        cursor.close() # 为什么这里会发生异常而不是execute
        conn.close()
                
                

                
                